package report

import (
	"github.com/lzzyuhyy/framework/mysql"
	"gorm.io/gorm"
)

// BrushReportUser 用户刷题记录报表
type BrushReportUser struct {
	gorm.Model
	UserId          int64  `gorm:"column:user_id;type:int(11);comment:用户ID"`
	MasteryRate     string `gorm:"column:mastery_rate;type:varchar(20);comment:已掌握率"`
	UnfulfilledRate string `gorm:"column:unfulfilled_rate;type:varchar(20);comment:未掌握率"`
}

type Results struct {
	CategoriesId int
	Count        int
}

// GetNumByCateId 根据分类id获取题目数量
func GetNumByCateId() (*[]Results, error) {
	var results []Results

	err := mysql.CreateMySQLClient(func(db *gorm.DB) error {
		return db.Table("interviews").Select("categories_id, COUNT(*) as count").Group("categories_id").Scan(&results).Error
	})
	return &results, err
}

type Result struct {
	CategoryName    string
	GraspedCount    int
	NotGraspedCount int
}

// GetCateQuantity 获取分类掌握数,未掌握数
func GetCateQuantity(userId int) ([]Result, error) {
	var res []Result
	err := mysql.CreateMySQLClient(func(db *gorm.DB) error {
		return db.Raw("SELECT pc.cate_name AS category_name,  SUM(CASE WHEN ua.is_grasps = 1 THEN 1 ELSE 0 END) AS grasped_count, COUNT(i.id) - SUM(CASE WHEN ua.is_grasps = 1 THEN 1 ELSE 0 END) AS not_grasped_count FROM    interviews i JOIN     categories c ON i.categories_id = c.id JOIN     categories pc ON c.p_id = pc.id AND pc.p_id = 0 LEFT JOIN   user_answers ua ON i.id = ua.interviews_id AND ua.user_id = ? AND ua.deleted_at IS NULL WHERE   i.deleted_at IS NULL   AND c.deleted_at IS NULL    AND pc.deleted_at IS NULL GROUP BY  pc.cate_name", userId).Find(&res).Error
	})
	return res, err
}

type Res struct {
	CategoryName   string
	TotalQuestions int
}

// GetCateSumQuantity 获取分类总数量
func GetCateSumQuantity() ([]Res, error) {
	var r []Res
	err := mysql.CreateMySQLClient(func(db *gorm.DB) error {
		return db.Raw("SELECT  pc.cate_name AS category_name,   COUNT(i.id) AS total_questions FROM     interviews i JOIN    categories c ON i.categories_id = c.id JOIN     categories pc ON c.p_id = pc.id AND pc.p_id = 0 WHERE  i.deleted_at IS NULL   AND c.deleted_at IS NULL   AND pc.deleted_at IS NULL GROUP BY    pc.cate_name").Find(&r).Error
	})
	return r, err
}
